package com.atguigu.dao;

import com.atguigu.datasource.JdbcPoolV2;
import com.atguigu.entity.A;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * projectName: day04_jdbc
 *
 * @author: 赵伟风
 * time: 2021/11/26 17:29
 * description: 数据库的crud 增删改查
 */
public class DbCurd {


    /**
     * DML
     */
    @Test
    public void  testCUD() throws SQLException {

        //apache dbutils提供的数据的curd的工具api
        QueryRunner queryRunner = new QueryRunner();

        String sql = "insert into a (aid,aname) values (?,?);";

        /**
         * update DDL DML DCL TPL
         * return int
         * 参数：
         *   1.connection
         *   2.sql
         *   3.Object...params ? 占位符值 可变参数！参数要和sql语句中的?顺序一一对应！
         */
        //连接池
        Connection connection = JdbcPoolV2.getConnection();

        // = 1. 创建preparedStatement  2.setObject占位符赋值  3.执行sql executeUpdate()
        int rows = queryRunner.update(connection, sql, "10", "呵呵");

        System.out.println("rows = " + rows);
        connection.close();

    }


    /**
     * apache DBUtils DQL
     */
    @Test
    public void   testRead() throws SQLException {

        //1.创建dbutils对象 queryrunner

        QueryRunner queryRunner = new QueryRunner();

        String sql = "select * from a where aid > ?;";

        /**
         * query执行查询语句！
         *   参数1： connection
         *   参数2： sql
         *   参数3： rsh -> ResultSetHandler 结果集处理器 - table - class  一行数据 - 一个对象  resultSet -> 想要的结果
         *      1.返回一个map
         *        select * from a where aid = 1;
         *        aid  aname
         *         1    xx
         *        map - aid - 1 / aname - xx
         *   参数4： Object...params
         *
         */
        Connection connection = JdbcPoolV2.getConnection();

        // queryRunner.query = 1.创建statement 2.占位符  3.执行sql语句 4.结果集解析
        // Map<String, Object> map = queryRunner.query(connection, sql, new MapHandler(), 1);
        List<Map<String, Object>> list = queryRunner.query(connection, sql, new MapListHandler(), 1);

        System.out.println("list = " + list);

        connection.close();


    }

    /**
     * 查询数据库数据使用对象接值
     * @throws SQLException
     */
    @Test
    public void   testReadToEntity() throws SQLException {

        //1.创建dbutils对象 queryrunner

        QueryRunner queryRunner = new QueryRunner();

        String sql = "select * from a where aid > ?;";


        Connection connection = JdbcPoolV2.getConnection();

        /**
         * 如果想要返回对象请使用 BeanHandler / BeanListHandler(class)
         */
        List<A> a = queryRunner.query(connection, sql, new BeanListHandler<>(A.class), 1);

        System.out.println("a = " + a);

        connection.close();


    }



}
